/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.pac3.persistence.dao.impl;

import edu.uoc.tdp.pac3.configuration.PropertiesConfiguration;
import edu.uoc.tdp.pac3.domain.RequestNote;
import edu.uoc.tdp.pac3.persistence.dao.IRequestNoteDao;
import edu.uoc.tdp.pac3.persistence.exceptions.ErrorConnexioBD;
import edu.uoc.tdp.pac3.persistence.exceptions.ErrorSQL;
import edu.uoc.tdp.pac3.persistence.exceptions.ItemNoTrobat;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Gerard
 */
public class RequestNoteDao extends BaseDao<RequestNote> implements IRequestNoteDao {

    public RequestNoteDao() {
           super(PropertiesConfiguration.getInstance(), "tb_request_note");
    }

    @Override
    public List<RequestNote> findAll() {
        List<RequestNote> list = null;
        try {
            openConnection();
            ResultSet rs = buscar(getTableName());
            list = new ArrayList<RequestNote>();
            while (rs.next()) {
                RequestNote request = new RequestNote();
                request.setRequestId(rs.getInt("request_id"));
                request.setCreationDate(rs.getDate("creation_date"));
                request.setDescription(rs.getString("description"));
                request.setId(rs.getInt("check_id"));
                request.setUserId(rs.getInt("user_id"));
                list.add(request);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ItemNoTrobat ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return list;
    }

    @Override
    public boolean insert(RequestNote entity) {
        boolean result = false;
        try {
            openConnection();
            begin();
            StringBuilder sb = new StringBuilder();            
            sb.append("INSERT INTO ").append(getTableName()).append(" (request_id, user_id, creation_date, description) VALUES(?,?,?,?)");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());

            if(entity.getRequestId() != null){
               statement.setInt(1, entity.getRequestId());
            }
            
            if(entity.getUserId() != null){
                statement.setInt(2, entity.getUserId());
            }
            
            if(entity.getCreationDate() != null){
                statement.setDate(3, new Date(entity.getCreationDate().getTime()));
            }
            
            if(entity.getDescription() != null){
                statement.setString(4, entity.getDescription());
            }
            
            int i = statement.executeUpdate();
            if (i > 0) {
                result = true;
            }
            commit();
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return result;
    }

    @Override
    public boolean update(RequestNote entity) {
          boolean result = false;
        try {
            openConnection();
            begin();
            StringBuilder sb = new StringBuilder();
            sb.append("UPDATE ").append(getTableName()).append("SET ")  .append("request_id=?, ")
                                                                        .append("user_id=?, ")
                                                                        .append("creation_date=?, ")
                                                                        .append("description=?")
                                                       .append(" WHERE check_id=?");
            
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            
            if(entity.getRequestId() != null){
                statement.setInt(1, entity.getRequestId());
            }
            
            if(entity.getUserId() != null){
                statement.setInt(2, entity.getUserId());
            }
            
            if(entity.getCreationDate() != null){
                statement.setDate(3, new Date(entity.getCreationDate().getTime()));
            }
            
            if(entity.getDescription() != null){
                statement.setString(4, entity.getDescription());
            }
            
            statement.setInt(5, entity.getId());

            int i = statement.executeUpdate();
            if (i > 0) {
                result = true;
            }
            commit();
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return result;
    }

    @Override
    public boolean delete(RequestNote entity) {
        boolean result = false;
        try {
            openConnection();
            begin();
            StringBuilder sb = new StringBuilder();
            sb.append("DELETE FROM ").append(getTableName()).append(" WHERE check_id=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            statement.setInt(1, entity.getId());
            int i = statement.executeUpdate();
            if (i > 0) {
                result = true;
            }
            commit();
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return result;    
    }

    @Override
    public RequestNote findById(Integer id) {
        RequestNote requestNote = null;
        try {
            openConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ").append(getTableName()).append(" WHERE check_id=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                requestNote = new RequestNote();
                requestNote.setCreationDate(rs.getDate("creation_date"));
                requestNote.setDescription(rs.getString("description"));
                requestNote.setId(rs.getInt("check_id"));
                requestNote.setRequestId(rs.getInt("request_id"));
                requestNote.setUserId(rs.getInt("user_id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return requestNote;
    }
    
    public List<RequestNote> getRequestNotes(Integer requestId){
        List<RequestNote> requestNotes = null;
        try {
            openConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ").append(getTableName()).append(" WHERE request_id=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            statement.setInt(1, requestId);
            ResultSet rs = statement.executeQuery();
            requestNotes = new ArrayList<RequestNote>();
            while (rs.next()) {
                RequestNote requestNote = new RequestNote();
                requestNote = new RequestNote();
                requestNote.setCreationDate(rs.getDate("creation_date"));
                requestNote.setDescription(rs.getString("description"));
                requestNote.setId(rs.getInt("check_id"));
                requestNote.setRequestId(rs.getInt("request_id"));
                requestNote.setUserId(rs.getInt("user_id"));
                requestNotes.add(requestNote);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return requestNotes;
    }
    
    public List<RequestNote> getRequestNotes(Integer requestId, Integer userId){
        List<RequestNote> requestNotes = null;
        try {
            openConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ").append(getTableName()).append(" WHERE request_id=? and user_id=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            statement.setInt(1, requestId);
            statement.setInt(2, userId);
            ResultSet rs = statement.executeQuery();
            requestNotes = new ArrayList<RequestNote>();
            while (rs.next()) {
                RequestNote requestNote = new RequestNote();
                requestNote = new RequestNote();
                requestNote.setCreationDate(rs.getDate("creation_date"));
                requestNote.setDescription(rs.getString("description"));
                requestNote.setId(rs.getInt("check_id"));
                requestNote.setRequestId(rs.getInt("request_id"));
                requestNote.setUserId(rs.getInt("user_id"));
                requestNotes.add(requestNote);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return requestNotes;
    }
    
}
